package br.com.ffm.praticas.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import br.com.ffm.praticas.bean.Product;
import br.com.ffm.praticas.connection.ConnectionDB;
import br.com.ffm.praticas.constants.Constant;

public class ProductDAO {
	
	/**
	 * Method that return all Products
	 * @return List<Product>
	 * @throws SQLException
	 */
	public List<Product> getProducts() throws SQLException {
		String query = "SELECT * FROM product ORDER BY product_name";
		List<Product> list = new LinkedList<Product>();
		CategoryDAO categoryDAO = new CategoryDAO();
		ProductTypeDAO productTypeDAO = new ProductTypeDAO();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			Product product = new Product();
			product.setId(result.getInt("id"));
			if (result.getString("bar_code") != null)
				product.setBarCode(result.getString("bar_code"));
			if (result.getString("product_name") != null)
				product.setProductName(result.getString("product_name"));
			product.setCategory(categoryDAO.getCategory(result.getInt("category_id")));
			product.setProductType((productTypeDAO.getProductType(result.getInt("product_type_id"))));
			product.setUnitValue(result.getDouble("unit_value"));
			list.add(product);
		}
		ConnectionDB.getInstance().close();
		return list;
	}
	
	/**
	 * Method that returns a specific Product for your id
	 * @param id Product Identifier
	 * @return Product
	 * @throws SQLException
	 */
	public Product getProduct(int id) throws SQLException {
		String query = "SELECT * FROM product WHERE (id = " + id + " )";
		Product product;
		CategoryDAO categoryDAO = new CategoryDAO();
		ProductTypeDAO productTypeDAO = new ProductTypeDAO();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			product = new Product();
			product.setId(result.getInt("id"));
			if (result.getString("bar_code") != null)
				product.setBarCode(result.getString("bar_code"));
			if (result.getString("product_name") != null)
				product.setProductName(result.getString("product_name"));
			product.setCategory(categoryDAO.getCategory(result.getInt("category_id")));
			product.setProductType((productTypeDAO.getProductType(result.getInt("product_type_id"))));
			product.setUnitValue(result.getDouble("unit_value"));
			ConnectionDB.getInstance().close();
			return product;
		}
		ConnectionDB.getInstance().close();
		return null;
	}
	
	/**
	 * Method that returns a specific Product for your name
	 * @param productName Product Name
	 * @return Product
	 * @throws SQLException
	 */
	public Product getProductForName(String productName) throws SQLException {
		String query = "SELECT * FROM product WHERE (product_name = '" + productName + "' )";
		Product product;
		CategoryDAO categoryDAO = new CategoryDAO();
		ProductTypeDAO productTypeDAO = new ProductTypeDAO();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			product = new Product();
			product.setId(result.getInt("id"));
			if (result.getString("bar_code") != null)
				product.setBarCode(result.getString("bar_code"));
			if (result.getString("product_name") != null)
				product.setProductName(result.getString("product_name"));
			product.setCategory(categoryDAO.getCategory(result.getInt("category_id")));
			product.setProductType((productTypeDAO.getProductType(result.getInt("product_type_id"))));
			product.setUnitValue(result.getDouble("unit_value"));
			ConnectionDB.getInstance().close();
			return product;
		}
		ConnectionDB.getInstance().close();
		return null;
	}
	
	/**
	 * Method that returns a specific Product for your bar code
	 * @param barCode Product Bar Code
	 * @return Product
	 * @throws SQLException
	 */
	public Product getProductForBarCode(String barCode) throws SQLException {
		String query = "SELECT * FROM product WHERE (bar_code = '" + barCode + "' )";
		Product product;
		CategoryDAO categoryDAO = new CategoryDAO();
		ProductTypeDAO productTypeDAO = new ProductTypeDAO();
		ConnectionDB.getInstance().getConnection();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			product = new Product();
			product.setId(result.getInt("id"));
			if (result.getString("bar_code") != null)
				product.setBarCode(result.getString("bar_code"));
			if (result.getString("product_name") != null)
				product.setProductName(result.getString("product_name"));
			product.setCategory(categoryDAO.getCategory(result.getInt("category_id")));
			product.setProductType((productTypeDAO.getProductType(result.getInt("product_type_id"))));
			product.setUnitValue(result.getDouble("unit_value"));
			ConnectionDB.getInstance().close();
			return product;
		}
		ConnectionDB.getInstance().close();
		return null;
	}
	
	/**
	 * Method that inserts a Product
	 * @param product Product that will be inserted
	 * @return Product Id
	 * @throws SQLException
	 */
	public int insert(Product product) throws SQLException {
		String query = "INSERT INTO product (bar_code, product_name, category_id, product_type_id, unit_value) VALUES (";
		query += "'" + product.getBarCode() + "', ";
		query += "'" + product.getProductName().replaceAll("'", "") + "', ";
		query += product.getCategory().getId() + ", ";
		query += product.getProductType().getId() + ",";
		query += product.getUnitValue() + ")";
		ConnectionDB.getInstance().executeCommand(query);
		
		query = "SELECT MAX(id) FROM product";
		int productId = ConnectionDB.getInstance().executeScalar(query, Constant.SCALAR_COMMAND_MAX, "id");
		return productId;
	}
	
	/**
	 * Method that deletes a Product
	 * @param product Product that will be deleted
	 * @return true it will have been deleted or false if it will not be deleted
	 */
	public boolean delete(Product product) {
		String query = "DELETE FROM product WHERE (id = " + product.getId() + " )";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Method that updates a Product
	 * @param product Product that will be updated
	 * @return true it will have been updates or false if it will not be updates
	 */
	public boolean update(Product product) {
		String query = "UPDATE product SET ";
		query += "bar_code = '" + product.getBarCode() + "', ";
		query += "product_name = '" + product.getProductName() + "', ";
		query += "category_id = " + product.getCategory().getId() + ", ";
		query += "product_type_id = " + product.getProductType().getId() + ", ";
		query += "unit_value = " + product.getUnitValue() + " ";
		query += "WHERE (id = " + product.getId() + " )";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
			return false;
		}
	}

}
